
/*
{
"状态":1,
"名称":"获取-款图片地址",
"作者":"",
"创建时间":"2018-12-25",
"功能":"获取-款图片地址",
"log": [
	{"date":"" , "dev" :"" , "content" : ""}
]
}
*/

 
--前端调用
DECLARE @styleno varchar(100)
DECLARE @colorid varchar(10)
DECLARE @sex VARCHAR(10)
DECLARE @Category  VARCHAR(20)
DECLARE @Category2  VARCHAR(20)
DECLARE @season  VARCHAR(20)
--定义变量
DECLARE @year CHAR(4)=''
declare @sql nvarchar(max)
declare @where nvarchar(max)=''

set @styleno=dbo.ft_checkparavalue('{styleno}','styleno','')
set @colorid=dbo.ft_checkparavalue('{colorid}','colorid','')
set @sex=dbo.ft_checkparavalue('{sex}','sex','')
set @category=dbo.ft_checkparavalue('{category}','category','')
set @category2=dbo.ft_checkparavalue('{category2}','category2','')
set @season=dbo.ft_checkparavalue('{season}','season','')

SET @year=LEFT(@season,4)
SET @season=STUFF(@season,1,4,'')

set @sql='select  A.*,ISNULL(B.color,'''') AS color_name,sex,category,category2,year,season from r_style_pic  A LEFT  JOIN regent.dbo.dictcolor B  ON A.colorid=B.colorid
left join regent.dbo.goods c on A.styleno=c.goods_no
where 1=1 {where}'
  
if isnull(@styleno,'')<>''
begin
	 set @where =@where +' and styleno=@styleno'
end

if isnull(@colorid,'')<>''
begin
	 set @where =@where +' and colorid=@colorid'
end

if isnull(@sex,'')<>''
begin
	 set @where =@where +' and sex=@sex'
END

if isnull(@Category,'')<>''
begin
	 set @where =@where +' and Category=@Category'
END

if isnull(@Category2,'')<>''
begin
	 set @where =@where +' and Category2=@Category2'
END

if isnull(@year,'')<>''
begin
	 set @where =@where +' and year=@year'
END

if isnull(@season,'')<>''
begin
	 set @where =@where +' and season=@season'
END

set @sql=replace(@sql,'{where}',@where)
exec sp_executesql @sql,N'@styleno varchar(100),@colorid varchar(10),@sex varchar(10),@Category varchar(20),@Category2 varchar(20),@Year char(4),@Season varchar(10)' ,@styleno,@colorid,@sex,@Category,@Category2,@Year,@Season

 
 